#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


-----------需求1:每天/每月/每年线上线下以及新老学员的意向用户个数
insert into hive.edu_das.all_table
select
    year_date,
    month_date,
    day_date,
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    customer_relationship_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_relationship
where group_type_new=origin_type or group_type_new=clue_state
order by customer_relationship_amount desc ;

-- - 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
insert into hive.edu_das.area_table
select
    year_date,
    month_date,
    day_date,
    area,           -- 地区
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    customer_relationship_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_relationship
where  group_type_new='area';

-- - 每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
insert into hive.edu_das.subject_table
select
    year_date,
    month_date,
    day_date,
    itcast_subject_id,           -- 学科
    subject_name,
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    customer_relationship_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_relationship
where group_type_new='itcast_subject'
order by customer_relationship_amount desc;

-- - 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10

insert into hive.edu_das.school_table
select
    year_date,
    month_date,
    day_date,
    itcast_school_id,           -- 学科
    school_name,
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    customer_relationship_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_relationship
where  group_type_new='itcast_school';

-- - 每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
insert into hive.edu_das.origin_channel_table
select
    year_date,
    month_date,
    day_date,
    origin_channel, --来源渠道
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    customer_relationship_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_relationship
where group_type_new='origin_type';

-- - 每天/每月/每年==各咨询中心==线上线下以及新老学员的意向用户个数
insert into hive.edu_das.dep_name_table
select
    year_date,
    month_date,
    day_date,
    tdepart_id,
    dep_name,       --销售部门
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    customer_relationship_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_relationship
where  group_type_new='tdepart';

-- - 每天线上线下及新老学员的有效线索个数
insert into hive.edu_das.val_clue_table
select
    year_date,
    month_date,
    day_date,
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    val_clue_amount,
    group_type_new,
    dt_group_type
from hive.edu_dm.dm_clue
where dt_group_type='day'  ;

-- - 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
insert into hive.edu_das.ratio_table
select
    year_date,
    month_date,
    day_date,
    hour_date,
    origin_type,    ---线上线下
    clue_state,     -- 新老学员
    val_clue_amount,    --有效线索个数
    clue_amount,
    group_type_new,
    dt_group_type,
    concat(cast (cast (val_clue_amount as decimal(35,5)) / cast (if(clue_amount is not null ,coalesce (clue_amount,0),0) as decimal (35,5)) * 100 as varchar ),'%')  as ratio

from hive.edu_dm.dm_clue
where dt_group_type='hour' ;







